#########################
#   Author : Leon yu    #
#   Date : 2025/06/14   #
#   Id : SM2772         #
#########################

# 报表-销售明细
from datetime import datetime
import pandas as pd
from dbResps import dc
from dbResps import storeId, Log
# from bussinessApis import isNowDate


def getQuerySalesItemReportFromDB(startTime:int, endTime:int):
    getQuerySalesItemReport = dict()
    # 总销售额
    getQuerySalesItemReport['SALES_AMOUNT'] = 0
    # 毛利润总和
    getQuerySalesItemReport['TOTAL_GP'] = 0
    
    
    getQuerySalesItemReport['salesReportModelList'] = list()
    
    # 查询order表
    orderTableName = 'order'
    orderColumns = "order_id as orderIds"
    orderWhere = f'store_id = "{storeId}" and `status` = \"SETTLED\" and (UNIX_TIMESTAMP(settle_time)*1000 between {startTime} and {endTime})'
    orderResp = dc.select(tableName=orderTableName, where=orderWhere, columns=orderColumns)
    orderDf = pd.DataFrame(orderResp)
    if orderDf.shape[0] > 0:
        orderIds2Tuple = dc.tuple2Str(tuple(orderDf['orderIds'].to_list()))
        orderDetailTableName = 'order_detail'
        orderDetailWhere = f'store_id = "{storeId}" and order_id in {orderIds2Tuple}'
        orderDetailResp = dc.select(tableName=orderDetailTableName, where=orderDetailWhere)
        orderDetailRespDf = pd.DataFrame(orderDetailResp)
        getQuerySalesItemReport['SALES_AMOUNT'] = orderDetailRespDf['actual_amount'].sum()
        getQuerySalesItemReport['TOTAL_GP'] = int((orderDetailRespDf['actual_amount'].astype(int)-orderDetailRespDf['cost_price'].astype(int))*orderDetailRespDf['quantity'].astype(int)/1000)
        
        # 毛利润
        grossProfit = 0
        for row in orderDetailRespDf.itertuples():
            salesReportModelListDictTmp = dict()
            actual_amount = int(getattr(row, 'actual_amount'))
            cost_price = int(getattr(row, 'cost_price'))
            quantity = int(getattr(row, 'quantity'))
            grossProfit += int((actual_amount-cost_price))*quantity/1000
            salesReportModelListDictTmp[getattr(row, 'sku_id')] = {
                # 销售额
                'salesPrice' : int(getattr(row, 'actual_amount')), 
                # 销售数量
                'sales' : int(int(getattr(row, 'quantity'))/1000),
                # 毛利润
                'gp' : int(grossProfit)
            }
            getQuerySalesItemReport['salesReportModelList'].append(salesReportModelListDictTmp)
    return  getQuerySalesItemReport
        
